Course Overview

Relational Database Design and Modeling for Software Engineers

Rate this course

Ask Author

Contribute

Back to course home

0% completed

Vote For New Content

Introduction

Course Overview

Before the Advent of Database

Fundamental Concepts

What is a Database?

Database management systems (DBMS)

Overview of Relational Databases

Relational Vs. Non-Relational Databases

Data Modeling

Introduction to Data Modeling

Levels of Data Modeling

Data Modeling Process

Best Practices in Data Modeling

Quiz

Entity-Relationship (ER) Data Modeling

Introduction to ER Models

Entities, Attributes, and Relationships

Cardinality and Participation

Attributes of Relationship Types

Weak Entity Types

Creating an ER Diagram for Employee Management System

Quiz

The Relational Data Model

Introduction to the Relational Model

Relations, Tuples, and Attributes

Keys in Relational Databases

Relational Integrity Constraints

Converting ER Diagrams to Relational Model

Best Practices for ER Diagrams to Relational Models

Quiz

Functional Dependency

Introduction to Functional Dependency

Types of Functional Dependencies

Inference Rules for Functional Dependencies

Closure of Attribute Sets

Quiz

Normalization

Introduction to Normalization

First Normal Form (1NF)

Second Normal Form (2NF)

Third Normal Form (3NF)

Boyce-Codd Normal Form (BCNF)

Higher Normal Forms (4NF, 5NF)

Exercise 1

Solution to Exercise 1

Exercise 2

Solution to Exercise 2

Case Studies

Designing a Hospital Management System

Designing a Hotel Management System

Designing a Bank Management System

Designing an Instagram

Designing an E-commerce Platform

Designing an Online Food Delivery System

Solution to Exercise 2

Step 1: Check for First Normal Form (1NF)

To determine if the given table is in 1NF, we need to ensure:

Each column contains atomic values (no multivalued or composite attributes).
Each row is unique and identifiable by a primary key.

Given Table:

Emp_ID	Emp_Name	DOB	Area	City	State	Zip
101	Alice	1990-05-01	Downtown	New York	NY	10001
102	Bob	1988-08-12	Midtown	New York	NY	10002
103	Charlie	1992-11-23	Central	Los Angeles	CA	90001
104	David	1985-03-15	West End	Chicago	IL	60601

Conclusion:

The table satisfies the requirements for First Normal Form (1NF).

Step 2: Check for Second Normal Form (2NF)

To determine if the table is in 2NF, we need to:

Ensure the table is already in 1NF.
Ensure there are no partial dependencies, where a non-key attribute depends only on part of a composite primary key.

Primary Key:

The primary key is Emp_ID (single column, not composite).

Dependencies:

All non-key attributes (Emp_Name, DOB, Area, City, State, Zip) depend directly on Emp_ID.

Conclusion:

There are no partial dependencies, and the table satisfies the requirements for Second Normal Form (2NF).

Step 3: Check for Third Normal Form (3NF)

To determine if the table is in 3NF, we need to:

Ensure the table is already in 2NF.
Ensure there are no transitive dependencies, where a non-key attribute depends on another non-key attribute rather than directly on the primary key.

Dependencies:

Emp_Name, DOB, and Zip depend directly on Emp_ID.
However, Area, City, and State depend on Zip, which in turn depends on Emp_ID. This is a transitive dependency.

Conclusion:

The table is not in 3NF due to the transitive dependency between Zip and Area, City, and State.

Step 4: Convert to Third Normal Form (3NF)

To remove the transitive dependency, we need to:

Create a separate table for the location details (dependent on Zip).
Retain a reference to Zip in the original table.

Employee Table

This table will store employee-specific information, with Zip as a foreign key.

Emp_ID (PK)	Emp_Name	DOB	Zip
101	Alice	1990-05-01	10001
102	Bob	1988-08-12	10002
103	Charlie	1992-11-23	90001
104	David	1985-03-15	60601

Explanation:

Emp_ID is the primary key.
All attributes in this table depend directly on Emp_ID.

Location Table

This table will store the location details based on Zip.

Zip (PK)	Area	City	State
10001	Downtown	New York	NY
10002	Midtown	New York	NY
90001	Central	Los Angeles	CA
60601	West End	Chicago	IL

Explanation:

Zip is the primary key.
Area, City, and State depend directly on Zip.

Final Relational Model

After normalization:

Employee Table:
- Contains Emp_ID, Emp_Name, DOB, and Zip.
- No transitive dependencies exist in this table.
Location Table:
- Contains Zip, Area, City, and State.
- Location details are fully normalized and uniquely identified by Zip.

Exercise 2

Designing a Hospital Management System

Mark as Completed

On this page

Step 1: Check for First Normal Form (1NF)

Step 2: Check for Second Normal Form (2NF)

Step 3: Check for Third Normal Form (3NF)

Step 4: Convert to Third Normal Form (3NF)

Final Relational Model